package com.tz.farmer.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tz.farmer.model.AuthenticationInfo;
import com.tz.farmer.model.OperateInfoVo;
import com.tz.farmer.model.ProductInfo;
import com.tz.farmer.model.ProductInfoVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface ProductInfoMapper extends BaseMapper<ProductInfo> {




    @Select("<script>select \n" +
            " p.id,\n" +
            " p.lead_id,\n" +
            " p.p_name,\n" +
            " p.pick_time,\n" +
            " p.create_time,\n" +
            " p.weight,\n" +
            " b.business_name as bName,\n" +
            " b.business_address as addr, \n" +
            " b.city as city \n" +
            " from  product_info as p left join business_info as b  on p.lead_id = b.id\n" +
            " where b.is_delete=0  " +
            " <if test = \"businessCode != null   \"  > and b.unisc_id = #{businessCode} </if> \n" +
            " <if test = \"city != null   \"  > and b.city = #{city} </if> \n" +
            " <if test = \"id != null  \"  > and p.id = #{id} </if> \n" +
            " <if test = \"businessName != null and businessName.trim() !=''  \"  > and b.business_name like CONCAT('%', #{businessName},'%') </if> \n" +
            " <if test = \"pName != null and pName.trim() !=''  \" >and p.p_name like CONCAT('%', #{pName},'%')  </if> \n" +
            " <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(pick_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')    \n" +
            "     and STR_TO_DATE(pick_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if>  " +
            "  order by  p.create_time desc " +
            " limit #{n} , #{m}" +
            "</script>")
    List<ProductInfoVo> selectProductList(@Param("businessCode") String businessCode,@Param("city") String city,@Param("id") Integer id, @Param("startTime") String startTime, @Param("endTime") String endTime, @Param("businessName")String businessName, @Param("pName")String pName, @Param("n")int n, @Param("m")int m);

    @Select("<script>" +
            "select \n" +
            " count(0) "+
            " from  product_info as p left join business_info as b  on p.lead_id = b.id\n" +
            " where b.is_delete=0  " +
            " <if test = \"businessCode != null   \"  > and b.unisc_id = #{businessCode} </if> \n" +
            " <if test = \"city != null   \"  > and b.city = #{city} </if> \n" +
            " <if test = \"id != null   \"  > and p.id = #{id} </if> \n" +
            " <if test = \"businessName != null and businessName.trim() !=''  \"  > and b.business_name like CONCAT('%', #{businessName},'%') </if> \n" +
            " <if test = \"pName != null and pName.trim() !=''  \" >and p.p_name like CONCAT('%', #{pName},'%')  </if> \n" +
            " <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(pick_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')    \n" +
            "     and STR_TO_DATE(pick_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if>  " +
            "</script>")
    int countTotal(@Param("businessCode") String businessCode,@Param("city") String city,@Param("id") Integer id,@Param("businessName")String businessName,@Param("pName")String pName ,@Param("startTime") String startTime,@Param("endTime") String endTime);

    @Select("select   a.id,  a.p_name,  a.weight,  a.create_time,  a.pick_time,  a.lead_id , b.city ,b.id as bId, b.business_name as bName from product_info a INNER JOIN business_info b  on b.id = a.lead_id  where b.is_delete = 0  and a.lead_id = #{leadId}  limit #{n},#{m}")
    List<ProductInfo> selectByLeadId(@Param("leadId") Integer leadId, @Param("n")int n, @Param("m")int m);

    @Select("select   count(0) from product_info a INNER JOIN business_info b  on b.id = a.lead_id  where b.is_delete = 0  and a.lead_id = #{leadId}")
    int countNum(Integer leadId);


    @Select("<script>select b.city from  product_info p, business_info  b where p.lead_id = b.id and p.id = #{pInfoId}</script>")
    String selectCityByPid( @Param("pInfoId") Integer pInfoId);

}
